package com.epam.web_project.bank.dao.transaction_dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.epam.web_project.bank.dao.customer_dao.CustomerDAO;
import com.epam.web_project.bank.dao.factory.MySQLDAOFactory;
import com.epam.web_project.bank.db.ConnectionPool;
import com.epam.web_project.bank.entity.Account;
import com.epam.web_project.bank.entity.Customer;
import com.epam.web_project.bank.entity.Transaction;
import com.epam.web_project.bank.exception.UnimplemetedException;
import com.epam.web_project.bank.util.AppPropertiesLoader;
import com.epam.web_project.bank.util.CurrencyStringConverter;

public class MySQLTransactionDAO implements TransactionDAO {
	private PreparedStatement ps = null;
	private ResultSet resultSet = null;
	private Connection conn = null;
	private Logger logger = Logger.getLogger(MySQLTransactionDAO.class);
	private AppPropertiesLoader propLoader = AppPropertiesLoader.getInstance();
	private final String FIND_AFFORDABLE_FUNDS_QUERY = "select a.amount, "
			+ "a.credit_lim from accounts a where a.id=?;";
	private final String FIND_RECEIVER_AMOUNT_QUERY = "select amount "
			+ "from accounts where id=?;";
	private final String INSERT_TRANSACTIONS_QUERY = "insert into transactions "
			+ "(acc_from,acc_to,amount_sent,amount_received,note) values "
			+ "(?,?,?,?,?);";
	private final String UPDATE_SENDER_ACCOUNT_QUERY = "update accounts "
			+ "set amount=?,credit_lim=? where id=?;";
	private final String UPDATE_RECEIVER_ACCOUNT_QUERY = "update accounts "
			+ "set amount=? where id=?;";
	private final String FIND_BY_RECEIVER_QUERY = "select cards.number as "
			+ "sender_card, concat_ws(' ',u.first_name, u.last_name) as "
			+ "sender_name,tr.amount_received,acc.currency,tr.dt,tr.note "
			+ "from accounts acc inner join transactions tr on "
			+ "acc.id=tr.acc_to inner join customer_account ca on "
			+ "tr.acc_from=ca.account_id inner join users u on u.id=ca.cust_id"
			+ " inner join accounts ac on ac.id=tr.acc_from inner join cards "
			+ "on cards.id=ac.card_id where tr.acc_to=?;";
	private final String FIND_BY_SENDER_QUERY = "select cards.number as "
			+ "dest_card,concat_ws(' ',u.first_name, u.last_name) as "
			+ "dest_name,tr.amount_sent,ac.currency,tr.dt,tr.note from "
			+ "accounts ac inner join transactions tr on ac.id=tr.acc_from "
			+ "inner join customer_account ca on tr.acc_to=ca.account_id "
			+ "inner join users u on u.id=ca.cust_id inner join accounts acc"
			+ " on acc.id=tr.acc_to inner join cards on cards.id=acc.card_id "
			+ "where tr.acc_from=?;";
	private final String CHECK_RECEIVER_ACCOUNT_QUERY = "select * from accounts"
			+ " a where a.id=? and a.on_approval='0' and a.deleted='0';";

	@Override
	public String save(Transaction transaction) {
		conn = MySQLDAOFactory.takeConnection();
		try {
			if (conn != null && !conn.isClosed()) {
				// check if the receiver's account isn't deleted or unapproved
				PreparedStatement psCheckReceiver = conn
						.prepareStatement(CHECK_RECEIVER_ACCOUNT_QUERY);
				psCheckReceiver.setInt(1, transaction.getAccountToId());
				ResultSet rsCheckReceiver = psCheckReceiver.executeQuery();
				if (!rsCheckReceiver.next()) {
					return propLoader.getProperty("RECEIVER_ACC_DELETED");
				}
				// check if the sender has enough money to transfer
				PreparedStatement psCheck = conn
						.prepareStatement(FIND_AFFORDABLE_FUNDS_QUERY);
				psCheck.setInt(1, transaction.getAccountFromId());
				ResultSet rsCheck = psCheck.executeQuery();
				if (rsCheck.next()) {
					BigDecimal debit = rsCheck.getBigDecimal(1);
					BigDecimal credit = rsCheck.getBigDecimal(2);
					BigDecimal funds = debit.add(credit);
					BigDecimal sent = transaction.getAmountSent();
					if (funds.compareTo(sent) >= 0) {
						// if debit + credit allow to send the sum
						conn.setAutoCommit(false); // start transaction
						PreparedStatement psInsert = conn
								.prepareStatement(INSERT_TRANSACTIONS_QUERY);
						psInsert.setInt(1, transaction.getAccountFromId());
						psInsert.setInt(2, transaction.getAccountToId());
						psInsert.setBigDecimal(3, transaction.getAmountSent());
						psInsert.setBigDecimal(4,
								transaction.getAmountReceived());
						psInsert.setString(5, transaction.getNote());
						if (psInsert.executeUpdate() == 1) {
							// if transaction inserted update sender account,
							// i.e. substitute the sum from debit (and credit)
							PreparedStatement psUpdateSender = conn
									.prepareStatement(UPDATE_SENDER_ACCOUNT_QUERY);
							if (debit.compareTo(sent) >= 0) {
								BigDecimal leftDebit = debit.subtract(sent);
								psUpdateSender.setBigDecimal(1, leftDebit);
								psUpdateSender.setBigDecimal(2, credit);
							} else {
								BigDecimal temp = sent.subtract(debit);
								credit = credit.subtract(temp);
								psUpdateSender.setInt(1, 0);
								psUpdateSender.setBigDecimal(2, credit);
							}
							psUpdateSender.setInt(3,
									transaction.getAccountFromId());
							// find out the receiver's amount before the
							// transfer
							PreparedStatement psTotalReceiverAmount = conn
									.prepareStatement(FIND_RECEIVER_AMOUNT_QUERY);
							psTotalReceiverAmount.setInt(1,
									transaction.getAccountToId());
							ResultSet rsTotalReceiverAmount = psTotalReceiverAmount
									.executeQuery();
							BigDecimal totalAmountReceiver = new BigDecimal(0);
							if (rsTotalReceiverAmount.next()) {
								// sum up the receiver's total amount after the
								// transaction
								totalAmountReceiver = rsTotalReceiverAmount
										.getBigDecimal(1)
										.add(transaction.getAmountReceived());
							}
							// update receiver's amount
							PreparedStatement psUpdateReceiver = conn
									.prepareStatement(UPDATE_RECEIVER_ACCOUNT_QUERY);
							psUpdateReceiver.setBigDecimal(1,
									totalAmountReceiver);
							psUpdateReceiver.setInt(2,
									transaction.getAccountToId());
							if (psUpdateReceiver.executeUpdate() == 1
									&& psUpdateSender.executeUpdate() == 1) {
								conn.commit();// commit transaction
								return propLoader
										.getProperty("TRANSFER_SUCCESS");
							} else {
								conn.rollback();
								return propLoader
										.getProperty("TRANSACTION_FAILED");
							}
						} else {
							conn.rollback();
							return propLoader.getProperty("TRANSACTION_FAILED");
						}
					} else {
						return propLoader.getProperty("NOT_ENOUGH_MONEY");
					}
				} else {
					return propLoader.getProperty("SENDER_NOT_FOUND");
				}
			} else {
				return propLoader.getProperty("CONNECTION_FAILED");
			}
		} catch (SQLException e) {
			logger.error(e);
			return propLoader.getProperty("TRANSACTION_FAILED");
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
	}

	@Override
	public String adminSave(Transaction transaction) {
		conn = MySQLDAOFactory.takeConnection();
		AppPropertiesLoader props = AppPropertiesLoader.getInstance();
		try {
			if (conn != null && !conn.isClosed()) {
				PreparedStatement psClientAmount = conn
						.prepareStatement(FIND_RECEIVER_AMOUNT_QUERY);
				int receiverId = transaction.getAccountToId();
				psClientAmount.setInt(1, receiverId);
				ResultSet rsClientAmount = psClientAmount.executeQuery();
				if (rsClientAmount.next()) {
					BigDecimal oldAmount = rsClientAmount
							.getBigDecimal("amount");
					BigDecimal amountToPut = transaction.getAmountSent();
					BigDecimal newAmount = oldAmount.add(amountToPut);
					conn.setAutoCommit(false);
					PreparedStatement psUpdateAmount = conn
							.prepareStatement(UPDATE_RECEIVER_ACCOUNT_QUERY);
					psUpdateAmount.setBigDecimal(1, newAmount);
					psUpdateAmount.setInt(2, receiverId);
					if (psUpdateAmount.executeUpdate() == 1) {
						ps = conn.prepareStatement(INSERT_TRANSACTIONS_QUERY);
						ps.setInt(1, transaction.getAccountFromId());
						ps.setInt(2, receiverId);
						ps.setBigDecimal(3, amountToPut);
						ps.setBigDecimal(4, amountToPut);
						ps.setString(5, props.getProperty("DEPOSIT_NOTE"));
						if (ps.executeUpdate() == 1) {
							conn.commit();
							return props.getProperty("TRANSFER_SUCCESS");
						} else {
							conn.rollback();
							return propLoader.getProperty("TRANSACTION_FAILED");
						}
					} else {
						conn.rollback();
						return propLoader.getProperty("TRANSACTION_FAILED");
					}
				} else {
					return propLoader.getProperty("TRANSACTION_FAILED");
				}
			} else {
				return propLoader.getProperty("CONNECTION_FAILED");
			}
		} catch (SQLException e) {
			logger.error(e);
			return propLoader.getProperty("TRANSACTION_FAILED");
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
	}

	@Override
	public boolean markDeleted(Transaction transaction)
			throws UnimplemetedException {
		throw new UnimplemetedException();
	}

	@Override
	/*
	 * "current user is a sender" transactions, returns list of transactions
	 * where the user appears as a sender of money, partner is the money
	 * receiver
	 */
	public List<Transaction> findByAccountFrom(Account account) {
		conn = MySQLDAOFactory.takeConnection();
		List<Transaction> transactions = new ArrayList<Transaction>();
		try {
			if (conn != null && !conn.isClosed()) {
				ps = conn.prepareStatement(FIND_BY_SENDER_QUERY);
				ps.setInt(1, account.getId());
				resultSet = ps.executeQuery();
				while (resultSet.next()) {
					Transaction transaction = new Transaction();
					transaction.setPartnerCardNumber(resultSet
							.getString("dest_card"));
					transaction
							.setPartnerName(resultSet.getString("dest_name"));
					transaction.setAmountSent(resultSet
							.getBigDecimal("amount_sent"));
					transaction
							.setCurrency(CurrencyStringConverter
									.convertToCurrency(resultSet
											.getString("currency")));
					transaction.setDate(resultSet.getTimestamp("dt"));
					transaction.setNote(resultSet.getString("note"));
					transactions.add(transaction);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return transactions;
	}

	@Override
	/*
	 * returns list of transactions where the user appears as a receiver of
	 * money, partner is the money sender
	 */
	public List<Transaction> findByAccountTo(Account account) {
		conn = MySQLDAOFactory.takeConnection();
		List<Transaction> transactions = new ArrayList<Transaction>();
		try {
			if (conn != null && !conn.isClosed()) {
				ps = conn.prepareStatement(FIND_BY_RECEIVER_QUERY);
				ps.setInt(1, account.getId());
				resultSet = ps.executeQuery();
				while (resultSet.next()) {
					Transaction transaction = new Transaction();
					transaction.setPartnerCardNumber(resultSet
							.getString("sender_card"));
					transaction.setPartnerName(resultSet
							.getString("sender_name"));
					transaction.setAmountReceived(resultSet
							.getBigDecimal("amount_received"));
					transaction
							.setCurrency(CurrencyStringConverter
									.convertToCurrency(resultSet
											.getString("currency")));
					transaction.setDate(resultSet.getTimestamp("dt"));
					transaction.setNote(resultSet.getString("note"));
					transactions.add(transaction);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					MySQLDAOFactory.returnConnection(conn);
				}
				if (ps != null) {
					ps.close();
				}
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		return transactions;
	}

	@Override
	public List<Transaction> findIncomingByCustomer(Customer customer)
			throws UnimplemetedException {
		throw new UnimplemetedException();
	}

	@Override
	public List<Transaction> findOutgoingByCustomer(Customer customer)
			throws UnimplemetedException {
		throw new UnimplemetedException();
	}

}
